﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ORMAC.CodeGen
{
    /// <summary>
    /// Helper class to get stored proc information from database
    /// </summary>
    public class SPHelper
    {
        #region Private Members
        /// <summary>
        /// Query to get sproc output in test mode
        /// </summary>
        private const string queryString = @"set fmtonly on;exec [{0}] {1};set fmtonly off";
        #endregion
        /// <summary>
        /// Gets a list of stored proc output column names and types
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sprocName"></param>
        /// <returns></returns>
        public TableInfo GetReturnInfo(string connectionString,SProcInfo sproc,List<TableInfo> tables)
        {
            List<ReturnInfo> res = new List<ReturnInfo>();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand searchCommand = new SqlCommand())
                {
                    searchCommand.CommandType = CommandType.Text;
                    searchCommand.CommandText = string.Format(queryString, sproc.Name,CreateParamString(sproc));
                    searchCommand.Connection = connection;
                    //Create Data Adapter
                    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(searchCommand))
                    {
                        //Fill the data Table with the result
                        DataTable dt = new DataTable("test");
                        dataAdapter.Fill(dt);
                        foreach (DataColumn col in dt.Columns)
                        {
                            res.Add(new ReturnInfo
                            {
                                Name=col.ColumnName,
                                ParameterType=col.DataType.Name
                            });
                        }
                    }
                }

            }
            sproc.ReturnColumns = res;
            if (!GetSPReturnTable(sproc, tables))
            {
                return GetSPNewTable(sproc, tables);
            }
            return null;
        }
        /// <summary>
        /// Creates the information on the return type of the stored procedure and passes it to the calling method
        /// </summary>
        /// <param name="sproc"></param>
        /// <param name="tables"></param>
        /// <returns></returns>
        private TableInfo GetSPNewTable(SProcInfo sproc, List<TableInfo> tables)
        {
            TableInfo tInfo = new TableInfo();
            tInfo.Columns = new List<ColumnInfo>();
            tInfo.Name =tInfo.CodeName= sproc.Name.Replace(" ","")+"Result";
            foreach (var col in sproc.ReturnColumns)
            {
                tInfo.Columns.Add(new ColumnInfo
                {
                    ColumnType = new TypeInfo { Name = col.ParameterType,IsSqlType=false },
                    Name=col.Name,
                });
            }
            sproc.ReturnClass = tInfo;
            return tInfo;
        }

        private string CreateParamString(SProcInfo sproc)
        {
            if (sproc.Params == null)
                return string.Empty;
            string res=string.Empty;
            for (int i = 0; i < sproc.Params.Count; i++)
            {
                if (i > 0)
                    res += ",";
                res += sproc.Params[i].Name + "=" + SqlTypeMap.Default(sproc.Params[i].ParameterType.Name);
            }
            return res;
        }
        /// <summary>
        /// tries to find a match in tables for the stored proc output
        /// </summary>
        /// <param name="rInfos"></param>
        /// <param name="tables"></param>
        /// <returns></returns>
        public void GetSPExistingTable(SProcInfo sproc, List<TableInfo> tables)
        {
            if (tables == null)
                return;
            foreach (TableInfo table in tables)
            {
                //chek if all parameters exist in this table
                bool allExistInTheTable = true;
                foreach (var rInfo in sproc.ReturnColumns)
                {
                    bool exists = table.Columns.Find(c => c.Name == rInfo.Name)!=null;
                    allExistInTheTable &= exists;
                }
                if (allExistInTheTable && table.Columns.Count == sproc.ReturnColumns.Count){
                    sproc.ReturnClass=table;
                    return;
                }
            }
        }
        private bool GetSPReturnTable(SProcInfo sproc,List<TableInfo> tables)
        {
            GetSPExistingTable(sproc, tables);
            if(sproc.ReturnClass!=null)//No need for further processing as the returned type is identified
                return true;
            //stored procedure returns a dataset which can not be represented by one of the existing table classes
            return false;
        }
    }
}
